Skip Headers

Oracle® Database Advanced Security Administrator's Guide 10g Release 2 (10.2) Part Number B14268-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 3 Transparent Data Encryption

This chapter describes how to secure sensitive data within an Oracle database by using transparent data encryption, the feature that enables you to encrypt database columns and manage encryption keys. This chapter contains the following topics:

- About Transparent Data Encryption

- Using Transparent Data Encryption

- Managing Transparent Data Encryption

## 3.1 About Transparent Data Encryption

Oracle Database 10g uses authentication, authorization, and auditing mechanisms to secure data in the database, but not in the operating system files where the data is stored. To protect those files, Oracle Database 10g provides transparent data encryption. This feature enables you to protect sensitive data in database columns stored in operating system files by encrypting it. Then, to prevent unauthorized decryption, it stores encryption keys in a security module external to the database.

This section contains the following topics:

- Benefits of Using Transparent Data Encryption

- When to Use Transparent Data Encryption

- How Transparent Data Encryption Works

- Overview of Basic Transparent Data Encryption Operations

### 3.1.1 Benefits of Using Transparent Data Encryption

Transparent data encryption enables simple and easy encryption for sensitive data in columns without requiring users or applications to manage the encryption key. This freedom can be extremely important when addressing, for example, regulatory compliance issues. No need to use views to decrypt data, because the data is transparently decrypted once a user has passed necessary access control checks. Security administrators have the assurance that the data on disk is encrypted, yet handling encrypted data becomes transparent to applications.

### 3.1.2 When to Use Transparent Data Encryption

Use transparent data encryption to protect confidential data such as credit card and social security numbers without having to manage key storage or create auxiliary tables, views, and triggers. An application that processes sensitive data can use this feature to provide strong data encryption with little or no change to the application.

Do not, however, use transparent data encryption with these database features:

- Index types other than B-tree

- Range scan search through an index

- Large object datatypes such as `BLOB` and `CLOB`

- Original import/export utilities

- Other database tools and utilities that directly access data files

Applications that need to use these unsupported features can use the `DBMS_CRYPTO` package for their encryption needs.

Note: Enabling encryption on an existing table results in a full table update like any other ALTER TABLE operation that modifies table characteristics. Administrators should keep in mind the potential performance and redo log impact on the database server before enabling encryption on a large existing table.

### 3.1.3 How Transparent Data Encryption Works

Transparent data encryption is a key-based access control system. Even if the encrypted data is retrieved, it cannot be understood until authorized decryption occurs, which is automatic for users authorized to access the table.

When a table contains encrypted columns, a single key is used regardless of the number of encrypted columns. The keys for all tables containing encrypted columns are encrypted with the database server master key and stored in a dictionary table in the database. No keys are stored in the clear.

As shown in Figure 3-1, the master key of the server is stored in an external security module that is outside the database and accessible only to the security administrator. For this external security module, Oracle uses an Oracle wallet as described in this chapter. Storing the master key in this way prevents its unauthorized use. In addition to storing the master key, the Oracle wallet is also used to generate encryption keys and perform encryption and decryption.

Figure 3-1 Transparent Data Encryption Overview

Using an external security module separates ordinary program functions from encryption operations, making it possible to divide duties between database administrators and security administrators. Security is enhanced because no single administrator is granted complete access to all data.

See Also: "Using Transparent Data Encryption" for detailed information about the SQL syntax used for transparent data encryption. "Supported Encryption and Integrity Algorithms" for a list of the algorithms this feature supports.

### 3.1.4 Overview of Basic Transparent Data Encryption Operations

To use transparent data encryption, you must have the `ALTER SYSTEM` privilege and a valid password to the Oracle wallet. If an Oracle wallet does not exist, then a new one is created using the password specified in the SQL command.

See Also: "Oracle Wallet Management for Transparent Data Encryption"

To create a new master key and begin using transparent data encryption, issue the following command:

    ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY password

This command generates the database server master encryption key, which the server uses to encrypt the column encryption key for each table. No table columns in the database can be encrypted until the master key of the server has been set.

A master key that has been set remains accessible to the database until the database instance is shutdown. To load the master key after the database is restarted, use the following command:

    ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY password

To create a new table with encrypted columns, use the CREATE TABLE command in the following form:

    CREATE TABLE table_name ( column_name column_type ENCRYPT,....);

The `ENCRYPT` keyword against a column specifies that the column should be encrypted.

If an existing table has columns that require encryption, then use the `ALTER TABLE` command in the following form:

    ALTER TABLE table_name MODIFY ( column_name column_type ENCRYPT,...);

The `ENCRYPT` keyword against a column specifies that the column should be encrypted.

To disable access to all encrypted columns in the database, use the following command:

    ALTER SYSTEM SET WALLET CLOSE

The command disables access to the master key in the wallet and prevents access to data in the encrypted columns.

See Also: Examples of `ALTER SYSTEM` commands in Example 3-1 and Example 3-2. Examples of `ALTER TABLE` commands in Example 3-8, Example 3-9, and Example 3-10. Examples using SQL for encryption include Example 3-5, Example 3-6, and Example 3-7.

## 3.2 Using Transparent Data Encryption

Only a small number of steps are needed to configure and use this feature. This section contains these topics:

- Enabling Transparent Data Encryption

- Opening the Encrypted Wallet for Database Access to Encryption Keys

- Setting and Resetting the Master Key

- Creating Tables That Contain Encrypted Columns

- Specifying Columns for Encryption in Existing Tables

- Creating an Index on an Encrypted Column

- Adding or Removing Salt from an Encrypted Column

- Changing the Encryption Key or Algorithm on Tables Containing Encrypted Columns

### 3.2.1 Enabling Transparent Data Encryption

Before transparent data encryption can be enabled, the compatibility level for the database must be 10.2. To start using transparent data encryption, the security administrator must create a wallet and set a master key.

The wallet can be the default database wallet shared with other Oracle Database components or a separate wallet specifically used by transparent data encryption. Oracle recommends that, for greater security, a separate wallet be used to store transparent data encryption master keys.

See Also: "Oracle Wallet Management for Transparent Data Encryption" for more information on configuring transparent data encryption to use a separate wallet

### 3.2.2 Opening the Encrypted Wallet for Database Access to Encryption Keys

The external security module stores the encryption keys in an Oracle wallet. The database must load the master and column encryption keys into memory from the wallet before it can encrypt or decrypt columns. Use the following `ALTER SYSTEM` command to explicitly open the wallet:

    ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY password

where `password` is a string value specified in `sqlnet.ora` as the password to the wallet.

Once the wallet has been opened, it remains open until you shut down the database instance (or close it explicitly by issuing an `ALTER SYSTEM SET ENCRYPTION WALLET CLOSE` command). When you restart the instance, you must issue the `ALTER SYSTEM SET ENCRYPTION WALLET OPEN` command again.

If the schema does not have the `ALTER SYSTEM` privilege, or the wallet is unavailable, or an incorrect password is given, then the command returns an error and exits. If the wallet is already open, the command returns an error and takes no action. Example 3-1 shows an example of each usage case.

Example 3-1 Opening the External Security Module Wallet with ALTER SYSTEM

    ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "U83j10LLt8v";
    Wallet opened.
    ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "U83j10LLt8v";
    ORA-XXXXX: Wallet already opened.
    ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "U93j10LLt8v";
    ORA-XXXXX: Incorrect wallet password given.

#### 3.2.2.1 Using Wallets with Automatic Login Enabled for Transparent Data Encryption

The external security module can use wallets with the automatic login feature enabled. These wallets remain open all the time. The security administrator does not have to reopen the wallet after a database instance has been restarted. If your environment does not require the extra security provided by a wallet that must be explicitly opened for use, then you may use an autologin wallet.

See Also: "Using Auto Login" for information about autologin wallets and how to use them.

#### 3.2.2.2 Specifying an Additional Wallet Location in SQLNET.ORA

By default, the external security module stores encryption keys in the Oracle wallet specified in the `sqlnet.ora` configuration file. If no wallet location is specified in the `sqlnet.ora` file, then the default database wallet is used.

If you wish to use a wallet specifically for transparent data encryption, then you must specify a second wallet location in `sqlnet.ora` by using the `ENCRYPTION_WALLET_LOCATION` parameter.

See Also: "Sample sqlnet.ora File"for an example of the syntax used to set this parameter

### 3.2.3 Setting and Resetting the Master Key

The master key is stored in the external security module and is used to protect column encryption keys. By default, the master key is a random key generated by transparent data encryption. It can also be an existing key pair from a PKI certificate designated for encryption. To use transparent data encryption with PKI key pairs, the issuing certificate authority must be able to issue X.509v3 certificates with the key usage field marked for encryption.

Neither key type is more secure, but if you have already deployed PKI within your organization, then you can leverage such PKI services as key escrow and recovery. However, encryption using current PKI algorithms requires significantly more system resources than symmetric key encryption. Using a PKI key pair as a master key may result in greater performance degradation when accessing encrypted columns in the database.

To set or reset the master key, you use the same `ALTER SYSTEM` command. The following sections explain how and why to perform each of these tasks.

#### 3.2.3.1 Setting the Master Key for First Time Use of Transparent Data Encryption

Before encrypting any database columns, you must generate or set a master key. This master key is used to encrypt the column encryption key that is generated automatically when you issue a SQL command with the `ENCRYPT` clause on a database column.

To set the master key, use the following SQL syntax:

    ALTER SYSTEM SET ENCRYPTION KEY certificate_ID IDENTIFIED BY password

where

- `certificate_ID` is an optional string containing the unique identifier of a certificate stored in the security module. Use this parameter if you intend to use your PKI private key as your master key. This parameter has no default setting.

  You can search for a `certificate_ID` by querying the `V$WALLET` fixed view when the wallet is open. Only certificates that can be used as master keys by transparent data encryption are shown.

- `password` is the mandatory wallet password for the security module, with no default setting. It is case sensitive; enclose it in double-quotation marks.

See Also: Oracle Database SQL Reference for the SQL rules for supplying passwords.

If no wallet exists, then this command creates a new one at the wallet location specified in the `sqlnet.ora` parameter file. If no wallet location is specified in the `sqlnet.ora` file, then the default database wallet location is used. If an existing autologin wallet is present at the expected wallet location, then a new wallet is not created.

#### 3.2.3.2 Resetting the Master Key

Regenerate the master key only if it has been compromised. Frequent master key regeneration does not necessarily enhance system security. Security modules can store a large, but not infinite, number of keys, and frequent master key regeneration can exhaust all the available storage space.

To reset the master key, use the SQL syntax as shown in "Setting the Master Key for First Time Use of Transparent Data Encryption".

Note that the `ALTER SYSTEM SET ENCRYPTION KEY` command is a DDL command requiring the `ALTER SYSTEM` privilege, and it automatically commits any pending transactions. Example 3-2 shows a sample usage of this command.

Example 3-2 Setting or Resetting the Master Key To Use a PKI-Based Private Key

    ALTER SYSTEM SET ENCRYPTION KEY "j23lm781098dhb345sm" IDENTIFIED BY "p3812dH9E";

Note that for PKI-based keys, certificate revocation lists are not enforced because enforcing certificate revocation may lead to losing access to all encrypted information in the database.

### 3.2.4 Adding or Removing Salt from an Encrypted Column

Salt is a way to strengthen the security of encrypted data. It is a random string added to the data before it is encrypted, causing repetition of text in the clear to appear different when encrypted. Salt thus removes one method attackers use to steal data, namely, matching patterns of encrypted text.

To add or remove salt from encrypted columns, you again use the `ALTER TABLE MODIFY` command with either the `SALT` or `NO SALT` parameter specified with the `ENCRYPT` clause. Example 3-3 and Example 3-4 illustrate such commands.

Example 3-3 Adding Salt to an Encrypted Column

    ALTER TABLE employee MODIFY (first_name ENCRYPT SALT);

Example 3-4 Removing Salt from an Encrypted Column

    ALTER TABLE employee MODIFY (first_name ENCRYPT NO SALT);

To remove salt from an encrypted column before indexing it, use the syntax shown in Example 3-4. This command also re-encrypts the data.

### 3.2.5 Creating Tables Tha t Contain Encrypted Columns

To create relational tables with encrypted columns, specify the SQL `ENCRYPT` clause when you define database columns with the `CREATE TABLE` statement.

This section contains the following topics:

- Creating a Table with an Encrypted Column That Uses the Default Algorithm

- Creating a Table with an Encrypted Column Using a Non-Default Algorithm and No Salt

- Creating an Encrypted Column on an External Table

#### 3.2.5.1 Creating a Table with an Encrypted Column That Uses the Default Algorithm

By default, transparent data encryption uses AES with a 192-bit length key (`AES192`). If you use the `ENCRYPT` clause with no other modifiers, the column you specify will be encrypted by using AES192 as shown in Example 3-5.

Example 3-5 Creating a New Table with an Encrypted Column Using the Default Algorithm (AES192)

    CREATE TABLE employee (
         first_name VARCHAR2(128),
         last_name VARCHAR2(128),
         empID NUMBER,
         salary NUMBER(6) ENCRYPT
    );

#### 3.2.5.2 Creating a Table with an Encrypted Column Using a Non-Default Algorithm and No Salt

By default, transparent data encryption adds salt to cleartext before encrypting it. This makes it harder for attackers to steal the data through a brute force attack.

However, if you plan to index the encrypted column, you must use `NO SALT`. Example 3-6 shows how to specify the `NO SALT` parameter with the SQL `ENCRYPT` clause (`empID NUMBER ENCRYPT NO SALT`). It also shows the syntax for specifying a different encryption algorithm (`salary NUMBER(6) ENCRYPT USING '3DES168'`). Note that the string which specifies the algorithm must be enclosed in single quotation marks.

Example 3-6 Creating a New Table with an Encrypted Column Using 3DES168 and NO SALT

    CREATE TABLE employee (
         first_name VARCHAR2(128),
         last_name VARCHAR2(128),
         empID NUMBER ENCRYPT NO SALT,
         salary NUMBER(6) ENCRYPT USING '3DES168'
    );

#### 3.2.5.3 Creating an Encrypted Column on an External Table

The external table database feature enables you to access data in external sources as if it were in a table in the database. External tables can be updated using the `ORACLE_DATAPUMP` access driver. Otherwise, they are read-only.

See Also: Oracle Database Concepts for discussions of Schema Objects and Tables.

To encrypt specific columns in an external table, you specify the `ENCRYPT` clause when you define those columns. That specification causes a randomly generated key to be used to encrypt the columns.

However, if you intend to move your external table, that key will not be available in the new location. For such a table, you should specify your own password to encrypt the columns. Then, after you move the data, use the same password to regenerate the key so you can access encrypted column data in the new location.

Table partition exchange also requires a password-based column encryption key.

Example 3-7 Creating a New External Table with a Password-Generated Column Key

    CREATE TABLE emp_ext (
         first_name,
         last_name,
         empID,
         salary,
         ssn ENCRYPT IDENTIFIED BY "xIcf3T9u"
    )  ORGANIZATION EXTERNAL
       (
        TYPE ORACLE_DATAPUMP
        DEFAULT DIRECTORY "D_DIR"
        LOCATION('emp_ext.dat')
        )
        REJECT LIMIT UNLIMITED
    as select * from employee;

See Also: Oracle Database SQL Reference about CREATE TABLE, ENCRYPT, and the rules for passwords.

### 3.2.6 Specifying Columns for Encryption in Existing Tables

To add an encrypted column to an existing table, or to encrypt or decrypt an existing column, you use the `ALTER TABLE` SQL command with the `ADD` or `MODIFY` clause.

This section contains the following topics:

- Adding Encrypted Columns to Existing Tables

- Encrypting Unencrypted Columns

- Disabling Encryption on a Column

#### 3.2.6.1 Adding Encrypted Columns to Existing Tables

To add an encrypted column to an existing table, you use the `ALTER TABLE ADD` command, specifying the new column with the `ENCRYPT` clause as shown in Example 3-8.

Example 3-8 Adding Encrypted Columns to Existing Tables

    ALTER TABLE employee ADD (ssn VARCHAR2(11) ENCRYPT);

This command encrypts the new column with the default AES encryption algorithm, using a 192-bit key length (AES192) and adding salt to the cleartext data before encryption. However, if you plan to index this column use the `ENCRYPT` clause with the `NO SALT` parameter. For an example of this syntax, see "Adding or Removing Salt from an Encrypted Column".

#### 3.2.6.2 Encrypting Unencrypted Columns

To encrypt unencrypted columns, use the `ALTER TABLE MODIFY` command, specifying the unencrypted column with the `ENCRYPT` clause as shown in Example 3-9.

Example 3-9 Encrypting Unencrypted Columns

    ALTER TABLE employee MODIFY (first_name ENCRYPT);

This command encrypts the column using the default AES192 algorithm, because no other encryption algorithm was specified. Salt is added by default because the `NO SALT` parameter was not specified with `ENCRYPT`. In this respect, this example is similar to Example 3-8.

See Also: "Adding or Removing Salt from an Encrypted Column"which shows how to specify a different encryption algorithm and no salt

If a column must be indexed, you must specify that no salt be added.

#### 3.2.6.3 Disabling Encryption on a Column

It may be necessary to disable encryption for reasons of compatibility or performance. To disable column encryption, use the `ALTER TABLE MODIFY` command with the `DECRYPT` clause as shown in Example 3-10.

Example 3-10 Turning Off Column Encryption

    ALTER TABLE employee MODIFY (first_name DECRYPT);

### 3.2.7 C reating an Index on an Encrypted Column

To create an index on an encrypted column, you use the standard `CREATE INDEX` command. The column being indexed must have been encrypted without salt. Example 3-11 shows how to create an index on a column that has been encrypted without salt.

Example 3-11 Creating Index on a Column Encrypted Without Salt

    CREATE TABLE employee (
       first_name VARCHAR2(128),
       last_name VARCHAR2(128),
       empID NUMBER ENCRYPT NO SALT,
       salary NUMBER(6) ENCRYPT USING '3DES168'
    );
    CREATE INDEX employee_idx on employee (empID);

Note: You cannot create an index on a column that has been encrypted with salt. If you try to do this, an error (ORA-28338) is raised.

### 3.2.8 Changing t he Encryption Key or Algorithm on Tables Containing Encrypted Columns

Each table can have at most one encryption key for its columns. This key can be changed by using either the original encryption algorithm or a different algorithm specified in the `REKEY` command phrase. The examples in this section illustrate these capabilities. Good security practices include backing up the wallet before and after such changes.

Example 3-12 Changing the Encryption Key on Tables Containing Encrypted Columns

    ALTER TABLE employee REKEY;

Example 3-13 Changing the Encryption Key and Algorithm on Tables Containing Encrypted Columns

    ALTER TABLE employee REKEY USING '3DES168';

See Also: Oracle Database SQL Reference about `ALTER TABLE`

### 3.2.9 Supported Encryption and Integrity Algorithms

By default, transparent data encryption uses the Advanced Encryption Standard with a 192-bit length cipher key (AES192). In addition, salt is added by default to cleartext before encryption unless specified otherwise. Note that salt cannot be added to indexed columns that you want to encrypt. For indexed columns, choose the `NO SALT` parameter for the SQL `ENCRYPT` clause.

You can change encryption algorithms and encryption keys on existing encrypted columns by setting a different algorithm with the SQL `ENCRYPT` clause.

See Also: Example 3-6 for the correct syntax when choosing the `NO SALT` parameter for the SQL `ENCRYPT` clause "Changing the Encryption Key or Algorithm on Tables Containing Encrypted Columns" for syntax examples when setting a different algorithm with the SQL `ENCRYPT` clause

Table 3-1 lists the supported encryption algorithms.

Table 3-1 Supported Encryption Algorithms for Transparent Data Encryption

Algorithm | Key Size | Parameter Name
--- | --- | ---
Triple DES (Data Encryption Standard) | 168 bits | `3DES168`
AES (Advanced Encryption Standard) | 128 bits | `AES128`
AES | 192 bits (default) | `AES192`
AES | 256 bits | `AES256`

For integrity protection, the `SHA-1` hashing algorithm is used.

### 3.2.10 Datatypes That Can Be Encrypted with the Transparent Data Encryption Feature

The following datatypes can be encrypted using this feature:

- `CHAR`

- `DATE`

- `INTERVAL DAY TO SECOND`

- `INTERVAL YEAR TO MONTH`

- `NCHAR`

- `NUMBER`

- `NVARCHAR2`

- `RAW`

- `TIMESTAMP` (includes `TIMESTAMP WITH TIME ZONE` and `TIMESTAMP WITH LOCAL TIME ZONE`)

- `VARCHAR2`

### 3.2.11 Quick Reference: Transparent Data Encryption SQL Commands

Table 3-2 provides a summary of the SQL commands you can use to implement and manage transparent data encryption.

Table 3-2 Transparent Data Encryption SQL Commands Quick Reference

Task | SQL Command
--- | ---
Add encrypted column to existing table | `ALTER TABLE` `table_name` `ADD (``column_name` `datatype` `ENCRYPT);`
Create table and encrypt column | `CREATE TABLE <``table_name``> (``column_name datatype` `ENCRYPT);`
Encrypt unencrypted existing column | `ALTER TABLE` `table_name` `MODIFY (``column_name` `ENCRYPT);`
Master key: set or reset | `ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY <``password``>;`
Master key: set or reset to use PKI certificate | `ALTER SYSTEM SET ENCRYPTION KEY <``certificate_ID``>` `IDENTIFIED BY` <`password``>;`
Wallet: open to access master keys | `ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY <``password``>;`

## 3.3 Managing Transparent Data Encryption

This section contains these topics:

- Oracle Wallet Management for Transparent Data Encryption

- Backup and Recovery of Master Keys

- Export and Import of Tables with Encrypted Columns

- Performance Effects of Transparent Data Encryption

- Security Considerations for Using Transparent Data Encryption

- Transparent Data Encryption and Replication in Distributed Environments

- Transparent Data Encryption with OCI

- Transparent Data Encryption Data Dictionary Views

### 3.3.1 Oracle Wallet Management for Transparent Data Encryption

To store the master keys, transparent data encryption can use either the default database wallet shared by all Oracle components or a separate wallet. The wallet can be an autologin wallet that allows access to encrypted data without requiring a security administrator to explicitly open the wallet.

#### 3.3.1.1 Creating Wallets

To create wallets used by transparent data encryption, you use the `ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY` `password` command. If no wallet exists in the default or specified locations, transparent data encryption creates a wallet when setting the master key for the first time. A wallet is not be created if the `WALLET_LOCATION` parameter in the `sqlnet.ora` file does not specify a valid path. The password specified in the SQL command for setting the master key becomes the password to open the wallet.

In addition to the SQL command, you can also use the `mkwallet` command-line utility and Oracle Wallet Manager to create wallets. These are full-featured tools that allow you to create wallets and to view and modify their content.

See Also: Chapter 9, "Using Oracle Wallet Manager" for more information about Oracle Wallet Manager

#### 3.3.1.2 Using an Autologin Wallet

You can create an autologin wallet with the `mkwallet` utility or Oracle Wallet Manager. The autologin wallet allows convenient access to encrypted data across database instance restarts.

Transparent data encryption uses an autologin wallet only if it is available at the correct location and the SQL command to open an encrypted wallet has not already been executed. If an autologin wallet is being used, you must not use the `ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY` `password` command.

#### 3.3.1.3 Specifying a Separate Wallet for Transparent Data Encryption

By default, transparent database encryption uses the default database wallet or the wallet specified by the `WALLET_LOCATION` parameter in the `sqlnet.ora` configuration file. Because this wallet can be shared by other Oracle components, Oracle recommends that a separate wallet be used exclusively for storing master keys used by transparent data encryption. To designate a separate wallet, set the `ENCRYPTION_WALLET_LOCATION` parameter in the `sqlnet.ora` file to point to the wallet used exclusively by transparent data encryption.

When determining which wallet to use, transparent data encryption first attempts to use the wallet specified by the parameter `ENCRYPTION_WALLET_LOCATION`. If the parameter is not set, or no wallet is found at the specified location, then it attempts to use the wallet specified by the parameter `WALLET_LOCATION`. If this fails as well, then transparent data encryption looks for a wallet at the default database location.

### 3.3.2 Backup and Recovery of Master Keys

This section contains the following topics:

- Backup and Recovery of Oracle Wallet

- Backup and Recovery of PKI Key Pair

#### 3.3.2.1 Backup and Recovery of Oracle Wallet

Because the master keys are required to access any encrypted data, they must be properly backed up. This means that, because master keys reside in an Oracle wallet, the wallet should be periodically backed up in a secure location along with the database data files. You must back up a copy of the wallet whenever a new master key is set.

If you loose the wallet that stores the master key, you can restore access to encrypted data by copying the backed-up version of the wallet to the appropriate location. If the restored wallet was archived after the last time the master key was reset, then no additional action needs to be taken.

If the restored wallet does not contain the most recent master key, then you can recover old data up to the point when the master key was reset by rolling back the state of the database to that point in time. All modifications to encrypted columns after the master key was reset are lost.

#### 3.3.2.2 Backup and Recovery of PKI Key Pair

Transparent data encryption supports the use of PKI asymmetric key pairs as master keys. This enables it to leverage existing key backup, escrow, and recovery facilities from leading certificate authority vendors.

In current key escrow or recovery systems, the certificate authority with key recovery capabilities typically stores a version of the private key or a piece of information that helps recover the private key. If the private key is lost, the user can recover the original key and certificate by contacting the certificate authority and initiating a key recovery process.

Typically, the key recovery process is automated and requires the user to present certain authenticating credentials to the certificate authority. Transparent data encryption puts no restrictions on the key recovery process other than that the recovered key and its associated certificate be a PKCS#12 file that can be imported into an Oracle wallet. This requirement is consistent with the key recovery mechanisms of leading certificate authorities.

After obtaining the PKCS#12 file with the original certificate and private key, you need to create a new empty wallet in the same location as the previous wallet. To do this, you can use the `mkwallet` command line utility or Oracle Wallet Manager. You can then import the PKCS#12 file into the wallet by using the same utility. You should choose a strong password to protect the wallet.

After the wallet has been created and the correct certificates imported, log onto the database and execute the following command at the SQL prompt to complete the recovery process:

    ALTER SYSTEM SET ENCRYPTION KEY certificate_id IDENTIFIED BY wallet_password

To retrieve the `certificate_id` of the certificate in the wallet, query the `V$WALLET` fixed view after the wallet has been opened.

### 3.3.3 Export and Import of Tables with Encrypted Columns

When exporting tables containing encrypted columns, it is important that:

- The sensitive data remain unintelligible during transport

- Authorized users can decrypt that data after it is imported at the destination

Because the key for decryption is local to the server where the tables originally reside, decryption at the destination will not be possible using that key. Consequently, prior to exporting, the administrator re-keys the table(s) with a password key, which he then securely provides to the destination administrator.

Upon import, the destination administrator specifies the same password. The affected columns being imported are decrypted, enabling the receiving server to immediately re-encrypt those columns with a local server key. They are then ready for standard authorized use in their new location.

### 3.3.4 Performance Effects of Transparent Data Encryption

This feature affects performance only when data is retrieved from or inserted into an encrypted column. No reduction of performance occurs for such operations on other columns, even in a table containing encrypted columns.

The total performance effect depends on the number of encrypted columns and their frequency of access. The columns most appropriate for encryption are those containing the most sensitive data, including regulatory mandates.

Enabling transparent data encryption on columns in an existing table results in a full table update on all rows in the column as it encrypts all data stored within the column. This may cause the table to be inaccessible while encryption is being enabled. Using Online Redefinition to enable transparent data encryption allows the table to be available while it is being encrypted.

The redo log impact of a full table update on a large table should also be kept in mind. If transparent data encryption is being enabled on a very large table, then the redo log size might need to be increased to accommodate the operation.

### 3.3.5 Security Considerations for Using Transparent Data Encryption

Access to encrypted columns can be:

- Public

- Controlled by standard authentication and authorization procedures and policies

- Controlled by individual users who can grant access to others

Security considerations for transparent data encryption operate within the broader arena of total system security. Security administrators must identify the levels of risk to be addressed and the degrees of sensitivity in data maintained by the site. Costs and benefits must be evaluated for the alternative methods of achieving acceptable protections. In many cases, it makes sense to have separate security administrators, separate wallets, and protected backup procedures for encrypted materials. Having separate wallets permits auto-login for other Oracle components but preserves password protection for the wallet enabling transparent data encryption.

Additional security considerations apply to normal database and network operations when transparent data encryption is in use. Encrypted column data stays encrypted in the data files, undo logs, and redo logs, as well as in the buffer cache of the system global area (SGA). However, during expression evaluation it is decrypted, making it possible for decrypted data to appear in the swap file on the disk, potentially visible to privileged operating system users.

### 3.3.6 Transparent Data Encryption and Replication in Distributed Environments

In general, the methods and protections vary with the choice of the tool by which replication is accomplished. SQL*Loader, Dataguard, and import/export are methods to enable transporting tables with encrypted columns. They rely on using a password created precisely to protect the data during transmission. When the data is reconstituted at the receipt-point, the columns marked as encrypted are immediately re-keyed with a master key local to the new table location.

See Also: Manuals for SQL*Loader, Dataguard, and import/export.

When asynchronous SQL-based replication is used, information describing the update to be performed is encoded (but not encrypted) in a LOB and can remain in the deferred queue for some time. While in the deferred queue, the data is not protected. To minimize this risk, configure replication so that encrypted table information does not remain in the deferred queue for a long time.

### 3.3.7 Transparent Data Encryption with OCI

"Row-shipping" cannot be used, because the key to make the row usable is not available at the receipt-point.

### 3.3.8 Transparent Data Encryption Data Dictionary Views

The following three data dictionary views maintain information about the encryption algorithms used to encrypt columns:

- `DBA_ENCRYPTED_COLUMNS`

- `ALL_ENCRYPTED_COLUMNS`

  Shows the algorithm used to encrypt columns for all tables that are accessible to a particular user.

- `USER_ENCRYPTED_COLUMNS`

  Shows the algorithm used to encrypt columns for all tables in a particular user's schema.

See Also: Oracle Database Reference for a full description of these data dictionary views.